package com.rdgzs.framework.utilsTools;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

public class ExcelPoiUtils<T> {
	private int startColumnNum=0;//开始输出的行列号
	private int startRowNum=0;//开始输出 的行号
	private List<TitleBO> titles=new ArrayList();//标题    key：变量名   value:标题内容  titles为空时将输出所有内容标题为变量名
	private HSSFWorkbook workbook = new HSSFWorkbook();
	private HSSFFont titleFont=null;//标题行的字体
	private HSSFFont contentFont=null;//内容行的字体
	private HSSFCellStyle titleStyle=null;//标题样式
	private HSSFCellStyle contentStyle=null;//内容样式
	private HSSFDataFormat dateFormat=workbook.createDataFormat();
	private OutputStream out=null;//将生成的excel输出到指定的流中
	
	public ExcelPoiUtils(OutputStream stream) {
		out=stream;
		titleFont=this.getTitleHSSFFont();
		contentFont=this.getDefHSSFFont();
		titleStyle=this.getTitleHSSFStyle();
		contentStyle=this.getDefHSSFStyle();
	}
	
	public static ExcelPoiUtils getInstance(OutputStream stream) throws WriteException, IOException {
		return new ExcelPoiUtils(stream);
	}
	
	public short getDefDateFormat(){
		return dateFormat.getFormat("yyyy-MM-dd");
	}
	
	/**
	 * 默认的字体的设置
	 * @return
	 */
	public HSSFFont getDefHSSFFont() {
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFFont.COLOR_NORMAL); // 字体颜色 其他字体颜色如何设置？
		font.setFontHeightInPoints((short) 12); // 字体大小
		font.setFontName("宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 宽度
		font.setItalic(false); // 是否使用斜体
		font.setStrikeout(false); // 是否使用划线
		return font;
	}
	
	public OutputStream write() throws IOException{
		workbook.write(out);
		return out;
	}
	/**
	 * 标题的字体：加粗
	 * @return
	 */
	public HSSFFont getTitleHSSFFont() {
		HSSFFont font = this.getDefHSSFFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		return font;
	}
	
	/**
	 * 标题的样式
	 * @return
	 */
	public HSSFCellStyle getTitleHSSFStyle() {
		HSSFCellStyle style=getDefHSSFStyle();
		style.setFont(this.titleFont);	
		return style;
	}
	
	/**
	 * 标题的样式
	 * @return
	 */
	public HSSFCellStyle getDefHSSFStyle() {
		HSSFCellStyle style = workbook.createCellStyle();
		style.setFont(this.contentFont);
		style.setFillBackgroundColor(HSSFFont.COLOR_NORMAL);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		return style;
	}
	
	/**
	 * 增加一个Sheet
	 * @param sheetName
	 * @return
	 * @throws IOException
	 */
	public HSSFSheet addSheet(String sheetName) throws IOException{
		return (sheetName!=null&&sheetName.trim().length()>0)?workbook.createSheet(sheetName):workbook.createSheet();
	}
	
	public HSSFSheet addSheet(List<T> objList,String sheetName) throws IOException, RowsExceededException, WriteException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		return this.buildSheet(addSheet(sheetName),objList);
	}
	
	public HSSFSheet buildSheet(HSSFSheet sheet,List<T> objList) throws RowsExceededException, WriteException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
		buildTitle(sheet,titleStyle);
		objs2sheet(objList,sheet);
		return sheet;
	}
	/**
	 * 将一个对像转为列值
	 * @return
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public void buildTitle(HSSFSheet sheet,HSSFCellStyle titleStyle) {
		int i=startColumnNum;
		HSSFCell cell = null;
		HSSFRow row =sheet.createRow((short) startRowNum);
		for(TitleBO bo:titles){
			cell =row.createCell(i);
			if(bo.getWidth()!=null){//列是否自动调整
				sheet.setColumnWidth(i, bo.getWidth()*256);
			}else if(bo.isAutoSizeCol()){
				sheet.autoSizeColumn(i);
			}
			cell.setCellValue(bo.getTitle());//设置单元格内容
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);//指定单元格格式：数值、公式或字符串
			if(titleStyle!=null){//标题内容 格式化
		         cell.setCellStyle(titleStyle);//设置单元格样式
			}
			i++;
		}
	}
	
	public HSSFSheet objs2sheet(List<T> objList,HSSFSheet sheet) throws RowsExceededException, WriteException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		//1.标头内容、样式、列宽初始化 
		//2.内容初始化 list<Map>
		int y=startColumnNum;
		int x=startRowNum;
		for (T obj : objList) {
			Class classz=obj.getClass();
			HSSFRow row =sheet.createRow((short) ++x);
			y=startColumnNum;
			for(TitleBO bo:titles){
				Method method=classz.getMethod(bo.getterName(), null);
				Object returnObj=method.invoke(obj, null);
				Class returnType=method.getReturnType();
				HSSFCell cell =row.createCell(y++);
				
				if(this.contentStyle!=null){
					cell.setCellStyle(contentStyle);//设置单元格样式
				}
				if(returnType == String.class){
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);//指定单元格格式：数值、公式或字符串
					cell.setCellValue((String)returnObj);//设置单元格内容
				}else if(returnType == Date.class){
					//cell.setCellType(HSSFCell.);//指定单元格格式：数值、公式或字符串
					cell.setCellValue((Date)returnObj);//设置单元格内容
				}else if(returnType == Integer.class ){
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//指定单元格格式：数值、公式或字符串
					cell.setCellValue((Integer)returnObj);//设置单元格内容
				}else if(returnType == Long.class ){
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//指定单元格格式：数值、公式或字符串
					cell.setCellValue((Long)returnObj);//设置单元格内容
				}else if(returnType == Double.class){
					cell.setCellValue((Double)returnObj);//设置单元格内容
				}
			}
		}
		
		return sheet;
	}
	
	/**
	 * 增加要显示的字段和显示名称
	 * @param title
	 * @param varName
	 * @return
	 */
	public TitleBO addTitle(String title,String varName){
		TitleBO bo=new TitleBO();
		bo.setTitle(title);
		bo.setVarName(varName);
		titles.add(bo);
		return bo;
	}
	
	
	
	
	public int getStartColumnNum() {
		return startColumnNum;
	}

	public void setStartColumnNum(int startColumnNum) {
		this.startColumnNum = startColumnNum;
	}

	public int getStartRowNum() {
		return startRowNum;
	}

	public void setStartRowNum(int startRowNum) {
		this.startRowNum = startRowNum;
	}

	public List<TitleBO> getTitles() {
		return titles;
	}

	public void setTitles(List<TitleBO> titles) {
		this.titles = titles;
	}

	public HSSFWorkbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(HSSFWorkbook workbook) {
		this.workbook = workbook;
	}

	public HSSFFont getTitleFont() {
		return titleFont;
	}

	public void setTitleFont(HSSFFont titleFont) {
		this.titleFont = titleFont;
	}

	public HSSFFont getContentFont() {
		return contentFont;
	}

	public void setContentFont(HSSFFont contentFont) {
		this.contentFont = contentFont;
	}

	public HSSFCellStyle getTitleStyle() {
		return titleStyle;
	}

	public void setTitleStyle(HSSFCellStyle titleStyle) {
		this.titleStyle = titleStyle;
	}

	public HSSFCellStyle getContentStyle() {
		return contentStyle;
	}

	public void setContentStyle(HSSFCellStyle contentStyle) {
		this.contentStyle = contentStyle;
	}

	public HSSFDataFormat getDateFormat() {
		return dateFormat;
	}

	public void setDateFormat(HSSFDataFormat dateFormat) {
		this.dateFormat = dateFormat;
	}

	public OutputStream getOut() {
		return out;
	}

	public void setOut(OutputStream out) {
		this.out = out;
	}




	public class TitleBO{
		private String title;
		private String varName;
		private Integer width;
		private boolean isAutoSizeCol=true;
		
		
		public String getTitle() {
			return title;
		}
		public void setTitle(String title) {
			this.title = title;
		}
		public String getVarName() {
			return varName;
		}
		public String getterName() {
			return "get"+varName.substring(0, 1).toUpperCase()+varName.substring(1);
		}
		public void setVarName(String varName) {
			this.varName = varName;
		}
		public boolean isAutoSizeCol() {
			return isAutoSizeCol;
		}
		public void setAutoSizeCol(boolean isAutoSizeCol) {
			this.isAutoSizeCol = isAutoSizeCol;
		}
		public Integer getWidth() {
			return width;
		}
		public void setWidth(Integer width) {
			this.width = width;
		}
		
	}
}


